﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

/// <summary>
/// Summary description for cDbJobs
/// </summary>

public struct strJob 
{
    public Guid JobId;
    public Guid CustomerId;
    public string JobCode;
    public string Description;
    public int Status;
    public System.DateTime StatusChanged;
    public string StatusNote;
}

public class cDbJobs
{
    public cDbJobs()
    {
        //
        // TODO: Add constructor logic here
        //
    }
    public static System.Data.DataSet getJobList(Guid custId, int status)
    {
        string sSql = string.Empty;
        if (custId != System.Guid.Empty)
        {
            sSql = "SELECT Jobs.JobId, Jobs.CustomerId, Customers.FullName, Jobs.JobCode, Jobs.Description, Jobs.Status FROM (Jobs LEFT OUTER JOIN Customers ON Jobs.CustomerId = Customers.CustomerId) WHERE Jobs.CustomerId = ?";
        }
        else
        {
            sSql = "SELECT Jobs.JobId, Jobs.CustomerId, Customers.FullName, Jobs.JobCode, Jobs.Description, Jobs.Status FROM (Jobs LEFT OUTER JOIN Customers ON Jobs.CustomerId = Customers.CustomerId)";
        }
        if (status > -1)
        {
            if (sSql.IndexOf("?") > -1)
            {
                sSql += " AND Status = " + status;
            }
            else
            {
                sSql += " WHERE Status = " + status;
            }
        }
        sSql += " ORDER BY JobCode;";
        System.Data.DataSet ds = new System.Data.DataSet();
        ds.Tables.Add("JOBLIST");
        try {
            System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            System.Data.OleDb.OleDbCommand cm = new System.Data.OleDb.OleDbCommand(sSql, cn);
            if (sSql.IndexOf("?") > -1) 
            {
                cm.Parameters.Add("CustomerId", System.Data.OleDb.OleDbType.Guid);
                cm.Parameters["CustomerId"].Value = custId;
            }
            cn.Open();
            System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(cm);
            da.Fill(ds,"JOBLIST");
            da.Dispose();
            cn.Close();
        }
        catch (System.Data.OleDb.OleDbException e)
        {
            throw e;
        }
        catch (Exception e)
        {
            throw e;
        }
        return ds;
    }
    public static strJob getJob(Guid id) 
    {
        strJob r = new strJob();
        try
        {
            System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            System.Data.OleDb.OleDbCommand cm = new System.Data.OleDb.OleDbCommand("SELECT JobId, CustomerId, JobCode, Description, Status, StatusChanged, StatusNote FROM Jobs WHERE JobId = ?;", cn);
            cm.Parameters.Add("JobId", System.Data.OleDb.OleDbType.Guid);
            cm.Parameters["JobId"].Value = id;
            cn.Open();
            System.Data.OleDb.OleDbDataReader dr = cm.ExecuteReader();
            while (dr.Read())
            {
                r.JobId = dr.GetGuid(0);
                r.CustomerId = dr.GetGuid(1);
                // JobCode, Description, Status, StatusChanged, StatusNote
            }
        }
        catch (System.Data.OleDb.OleDbException e)
        {
            throw e;
        }
        catch (Exception e)
        {
            throw e;
        }
        return r;
    }
}
